with complaint_detail as (
    select network_code
         , network_name
         , duty_type
         , delivery_code                                                                               --派件员编码
         , delivery_name                                                                               --派件员名称
         , dt
         , count(1)                                                           as complaint_cnt         --客诉量
         , sum(if((work_type = '普通工单' and second_type_code = '2104') or (work_type = '平台工单'), 1,
                  0))                                                         as xijia_sign_num        --虚假签收
         , sum(if((work_type = '普通工单' and second_type_name = '超时效催件'), 1, 0)) as over_time_num         --超时效催件
         , sum(if((work_type = '普通工单' and second_type_name = '有揽未发'), 1, 0))  as have_take_no_send_num --有揽未发
         , sum(if((work_type = '普通工单' and second_type_name = '不送货上门'), 1, 0)) as no_send_door_num      --不送货上门
         , sum(if((work_type = '普通工单' and second_type_name = '服务态度'), 1, 0))  as service_attitude_num  --服务态度
         , sum(if((work_type = '普通工单' and second_type_name = '费用问题'), 1, 0))  as money_problem_num     --费用问题
         , sum(if((work_type = '普通工单' and second_type_name = '外投意向'), 1, 0))  as out_complaint_num     --外投诉意向
         , sum(if((work_type = '普通工单' and second_type_name = '赔偿意向') or (work_type = '理赔工单'), 1,
                  0))                                                         as compensate_num        --赔偿意向
         , sum(if(reback_transfer_falg = 1, 1, 0))                            as reback_transfer_num   --退转件票数
    from (
             select waybill_no                                                                     --运单号
                  , duty_type                                                                      --责任类型
                  , work_order_no                                                                  --工单编号
                  , work_type                                                                      --工单类型
                  , second_type_code                                                               --二级问题类型编码
                  , second_type_name                                                               --二级问题类型名称
                  , reback_transfer_falg                                                           --退转件中:是否退回,1是0否
                  , delivery_code                                                                  --派件员编码
                  , delivery_name                                                                  --派件员名称
                  , if(work_type = '邮政工单', accept_network_code, duty_network_code) as network_code --统计网点编码
                  , if(work_type = '邮政工单', accept_network_name, duty_network_name) as network_name --统计网点名称
                  , dt
             from jms_dm.dm_waybill_customer_complaint_detail_new_dt
             where dt >= date_add(trunc('{{ execution_date  | cst_ds }}','MM'),-1)
               and dt <= '{{ execution_date  | cst_ds }}'
               and is_delete = 2
         ) t
    where network_code <> '888888'
    and network_code is not null
    group by network_code
           , network_name
           , duty_type
           , dt
           , delivery_code
           , delivery_name
),
     network_whole as (
         select code             --编号
              , name             --名称
              , network_type
              , virt_code        --虚拟代理区code
              , virt_name        --虚拟代理区name
              , manage_code      --管理大区名称
              , manage_name      --管理大区名称
              , regional_id      --大区ID
              , regional_desc    --regional_desc
              , agent_code       --代理区编码
              , agent_name       --代理区名称
              , fran_code        --加盟商编码
              , fran_name        --加盟商名称
              , center_code      --中心编码
              , center_name      --中心名称
              , provider_id      --省份ID
              , provider_desc    --省份
              , city_id          --城市ID
              , city_desc        --城市
              , area_id          --区域id
              , area_desc        --区域name
              , zone_id          --片区id
              , zone_code        --片区code
              , zone_name        --片区name
              , area_code --区域code
              , area_name --区域name
              , leaf_area_id      --区域name
         from jms_dim.dim_network_whole_massage
     ),
     network_arr_pick as (--网点/集散派件量和到件量
         select network_code
              , tak_cnt --收件量
              , arr_ctn --到件量
              , dt
         from jms_dm.dm_customer_complaint_network_arr_pick_summary_dt
         where dt >= date_add(trunc('{{ execution_date  | cst_ds }}','MM'),-1)
           and dt <= '{{ execution_date  | cst_ds }}'
           and date_type = 1
         group by network_code
                , tak_cnt --收件量
                , arr_ctn --到件量
                , dt
     ),
     result_table as (
         select t1.network_code                      --责任网点编码
              , t1.network_name                      --责任网点名称
              , t1.duty_type                         --责任类型
              , t1.delivery_code                     --派件员编码
              , t1.delivery_name                     --派件员名称
              , t1.complaint_cnt                     --客诉量
              , t1.xijia_sign_num                    --虚假签收
              , t1.over_time_num                     --超时效催件
              , t1.have_take_no_send_num             --有揽未发
              , t1.no_send_door_num                  --不送货上门
              , t1.service_attitude_num              --服务态度
              , t1.money_problem_num                 --费用问题
              , t1.out_complaint_num                 --外投诉意向
              , t1.compensate_num                    --赔偿意向
              , t1.reback_transfer_num               --退转件票数
              , t2.network_type                      --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
              , t2.virt_code                         --虚拟代理区code
              , t2.virt_name                         --虚拟代理区name
              , t2.regional_id                       --大区ID
              , t2.regional_desc                     --regional_desc
              , t2.manage_code                       --管理大区名称
              , t2.manage_name                       --管理大区名称
              , t2.agent_code                        --代理区编码
              , t2.agent_name                        --代理区名称
              , t2.fran_code                         --加盟商编码
              , t2.fran_name                         --加盟商名称
              , t2.center_code                       --中心编码
              , t2.center_name                       --中心名称
              , t2.provider_id                       --省份ID
              , t2.provider_desc                     --省份
              , t2.city_id                           --城市ID
              , t2.city_desc                         --城市
              , t2.area_id                           --区域id
              , t2.area_desc                         --区域name
              , t2.zone_code        as district_code --片区code
              , t2.zone_name        as district_desc --片区desc
              , t2.zone_id          as district_id   --片区id
              , nvl(t3.tak_cnt, 0)  as tak_cnt       --收件量
              , nvl(t3.arr_ctn, 0)  as arr_ctn       --到件量
              , t2.area_code as area_code     --区域code
              , t2.area_name as area_name     --区域name
              , t2.leaf_area_id      as area_id_t
              , t1.dt
         from complaint_detail t1
                  left join network_whole t2
                            on t1.network_code = t2.code
                  left join network_arr_pick t3
                            on t1.network_code = t3.network_code
                                and t1.dt = t3.dt
     )
insert
overwrite
table
jms_dm.dm_sqs_qiwei_complaint_network_summary_dt
partition
(
dt
)
select t1.network_code          --网点编码
     , t1.network_name          --网点名称
     , t1.delivery_code         --派件员编码
     , t1.delivery_name         --派件员名称
     , t1.network_type          --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
     , t1.duty_type             --类型:第三方,网点
     , t1.virt_code             --虚拟代理区code
     , t1.virt_name             --虚拟代理区name
     , t1.regional_id           --大区ID
     , t1.regional_desc         --regional_desc
     , t1.manage_code           --管理大区名称
     , t1.manage_name           --管理大区名称
     , t1.agent_code            --代理区编码
     , t1.agent_name            --代理区名称
     , t1.fran_code             --加盟商编码
     , t1.fran_name             --加盟商名称
     , t1.center_code           --中心编码
     , t1.center_name           --中心名称
     , t1.provider_id           --省份ID
     , t1.provider_desc         --省份
     , t1.city_id               --城市ID
     , t1.city_desc             --城市
     , t1.area_id               --区域id
     , t1.area_desc             --区域name
     , t1.district_code         --片区code
     , t1.district_desc         --片区desc
     , t1.district_id           --片区id
     , t1.complaint_cnt         --客诉量
     , t1.xijia_sign_num        --虚假签收
     , t1.over_time_num         --超时效催件
     , t1.have_take_no_send_num --有揽未发
     , t1.no_send_door_num      --不送货上门
     , t1.service_attitude_num  --服务态度
     , t1.money_problem_num     --费用问题
     , t1.out_complaint_num     --外投诉意向
     , t1.compensate_num        --赔偿意向
     , t1.reback_transfer_num   --退转件票数
     , t1.tak_cnt               --收件量
     , t1.arr_ctn               --到件量
     , t1.dt as date_time       --日期
     , t1.area_code             --区域code
     , t1.area_name             --区域name
     , t1.area_id_t             --区域id
     , t1.dt as dt              --分区
from result_table t1
    distribute by dt
;
